﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CLEX.DataContracts;

namespace CLEX.Server.DAL
{
    public sealed class UserDAL : IUserDAL
    {
        private const string USER_ID_COL = "user_id";
        private const string USERNAME_COL = "username";
        private const string HASHED_PASSWORD_COL = "hashed_password";
        private const string PASSWORD_SALT_COL = "password_salt";
        private const string EMAIL_COL = "email";
        private const string IS_ADMIN_COL = "is_admin";

        private const string GET_ALL_SP = "user_get_all";
        private const string GET_SP = "user_get";
        private const string CREATE_SP = "user_create";
        private const string UPDATE_SP = "user_update";
        private const string DELETE_SP = "user_delete";

        private readonly string connectionString;

        public UserDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public List<UserFullData> GetAll()
        {
            List<UserFullData> result = new List<UserFullData>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_ALL_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int userId = reader.GetOrdinal(USER_ID_COL);
                        int username = reader.GetOrdinal(USERNAME_COL);
                        int hashedPassword = reader.GetOrdinal(HASHED_PASSWORD_COL);
                        int passwordSalt = reader.GetOrdinal(PASSWORD_SALT_COL);
                        int email = reader.GetOrdinal(EMAIL_COL);
                        int isAdmin = reader.GetOrdinal(IS_ADMIN_COL);

                        while (reader.Read())
                        {
                            result.Add(new UserFullData()
                            {
                                UserId = reader.GetInt32(userId),
                                Username = reader.GetString(username),
                                HashedPassword = reader.GetString(hashedPassword),
                                PasswordSalt = reader.GetString(passwordSalt),
                                Email = reader.GetString(email),
                                IsAdmin = reader.GetBoolean(isAdmin),
                            });
                        }
                    }
                }
            }

            return result;
        }

        public UserFullData Get(int userId)
        {
            UserFullData result;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_ID_COL, userId);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int userIdCol = reader.GetOrdinal(USER_ID_COL);
                        int usernameCol = reader.GetOrdinal(USERNAME_COL);
                        int hashedPasswordCol = reader.GetOrdinal(HASHED_PASSWORD_COL);
                        int passwordSaltCol = reader.GetOrdinal(PASSWORD_SALT_COL);
                        int emailCol = reader.GetOrdinal(EMAIL_COL);
                        int isAdminCol = reader.GetOrdinal(IS_ADMIN_COL);

                        if (reader.Read())
                        {
                            result = new UserFullData()
                            {
                                UserId = reader.GetInt32(userIdCol),
                                Username = reader.GetString(usernameCol),
                                HashedPassword = reader.GetString(hashedPasswordCol),
                                PasswordSalt = reader.GetString(passwordSaltCol),
                                Email = reader.GetString(emailCol),
                                IsAdmin = reader.GetBoolean(isAdminCol),
                            };
                        }
                        else
                        {
                            result = null;
                        }
                    }
                }
            }

            return result;
        }

        public UserFullData Get(string username)
        {
            UserFullData result;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USERNAME_COL, username);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int userIdCol = reader.GetOrdinal(USER_ID_COL);
                        int usernameCol = reader.GetOrdinal(USERNAME_COL);
                        int hashedPasswordCol = reader.GetOrdinal(HASHED_PASSWORD_COL);
                        int passwordSaltCol = reader.GetOrdinal(PASSWORD_SALT_COL);
                        int emailCol = reader.GetOrdinal(EMAIL_COL);
                        int isAdminCol = reader.GetOrdinal(IS_ADMIN_COL);

                        if (reader.Read())
                        {
                            result = new UserFullData()
                            {
                                UserId = reader.GetInt32(userIdCol),
                                Username = reader.GetString(usernameCol),
                                HashedPassword = reader.GetString(hashedPasswordCol),
                                PasswordSalt = reader.GetString(passwordSaltCol),
                                Email = reader.GetString(emailCol),
                                IsAdmin = reader.GetBoolean(isAdminCol),
                            };
                        }
                        else
                        {
                            result = null;
                        }
                    }
                }
            }

            return result;
        }

        public UserFullData Create(UserFullData user)
        {
            UserFullData result = new UserFullData();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(CREATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    var userId = cmd.CreateParameter();
                    userId.Direction = ParameterDirection.Output;
                    userId.ParameterName = USER_ID_COL;
                    userId.Size = 4;
                    userId.DbType = DbType.Int32;
                    cmd.Parameters.Add(userId);
                    cmd.Parameters.AddWithValue(USERNAME_COL, user.Username);
                    cmd.Parameters.AddWithValue(HASHED_PASSWORD_COL, user.HashedPassword);
                    cmd.Parameters.AddWithValue(PASSWORD_SALT_COL, user.PasswordSalt);
                    cmd.Parameters.AddWithValue(EMAIL_COL, user.Email);
                    cmd.Parameters.AddWithValue(IS_ADMIN_COL, user.IsAdmin);
                    cmd.ExecuteNonQuery();

                    result.UserId = (int)userId.Value;
                }
            }

            result.Username = user.Username;
            result.Email = user.Email;
            result.HashedPassword = user.HashedPassword;
            result.IsAdmin = user.IsAdmin;
            return result;
        }

        public void Update(UserFullData user)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(UPDATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_ID_COL, user.UserId);
                    cmd.Parameters.AddWithValue(USERNAME_COL, user.Username);
                    cmd.Parameters.AddWithValue(HASHED_PASSWORD_COL, user.HashedPassword);
                    cmd.Parameters.AddWithValue(PASSWORD_SALT_COL, user.PasswordSalt);
                    cmd.Parameters.AddWithValue(EMAIL_COL, user.Email);
                    cmd.Parameters.AddWithValue(IS_ADMIN_COL, user.IsAdmin);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void Delete(UserFullData user)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(DELETE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_ID_COL, user.UserId);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
